// 文件名称: StudentDAO.java
// 创建日期: 2023-10-05
// 描述: 学生数据访问对象类，提供对数据库中学生表的操作方法。

package org.wisdom.studentchoose.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生数据访问对象类，提供对数据库中学生表的操作方法。
 */
public class StudentDAO {

    /**
     * 获取所有学生。
     *
     * @return 学生列表
     */
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students";

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String major = resultSet.getString("major");
                String className = resultSet.getString("class_name");
                String groupName = resultSet.getString("group_name");
                String studentNumber = resultSet.getString("student_number");
                double grade = resultSet.getDouble("grade");
                String gender = resultSet.getString("gender");
                students.add(new Student(id, name, major, className, groupName, studentNumber, grade, gender));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("从数据库中获取学生失败", e);
        }

        return students;
    }

    /**
     * 添加学生。
     *
     * @param student 要添加的学生对象
     */
    public void addStudent(Student student) {
        String sql = "INSERT INTO students (name, major, class_name, group_name, student_number, grade, gender) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            statement.setString(1, student.getName());
            statement.setString(2, student.getMajor());
            statement.setString(3, student.getClassName());
            statement.setString(4, student.getGroupName());
            statement.setString(5, student.getStudentNumber());
            statement.setDouble(6, student.getGrade());
            statement.setString(7, student.getGender());

            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("向数据库中添加学生失败", e);
        }
    }

    /**
     * 删除学生。
     *
     * @param id 要删除的学生的ID
     */
    public void deleteStudent(int id) {
        String sql = "DELETE FROM students WHERE id = ?";

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("从数据库中删除学生失败", e);
        }
    }

    /**
     * 获取所有班级名称。
     *
     * @return 班级名称列表
     */
    public static List<String> getClassNames() {
        List<String> classNames = new ArrayList<>();
        String sql = "SELECT name FROM `class`";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                classNames.add(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return classNames;
    }

    /**
     * 获取指定班级的所有小组名称。
     *
     * @param className 班级名称
     * @return 小组名称列表
     */
    public static List<String> getGroupNames(String className) {
        List<String> groupNames = new ArrayList<>();
        String sql = "SELECT name FROM `groups` WHERE class_name = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, className);
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    groupNames.add(resultSet.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return groupNames;
    }

    /**
     * 获取指定小组的所有学生。
     *
     * @param group 小组名称
     * @return 学生列表
     */
    public List<Student> getStudentsByGroup(String group) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students WHERE group_name = ?";

        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            statement.setString(1, group);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String major = resultSet.getString("major");
                String className = resultSet.getString("class_name");
                String groupName = resultSet.getString("group_name");
                String studentNumber = resultSet.getString("student_number");
                double grade = resultSet.getDouble("grade");
                String gender = resultSet.getString("gender");
                students.add(new Student(id, name, major, className, groupName, studentNumber, grade, gender));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("从数据库中获取指定小组的学生失败", e);
        }

        return students;
    }
}
